/*
 *  Copyright 2019-2020 Zheng Jie
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package me.zhengjie.modules.anniversary.repository;

import me.zhengjie.modules.anniversary.domain.GzmuAnnUserContacts;
import me.zhengjie.modules.anniversary.domain.Student;
import me.zhengjie.modules.anniversary.service.dto.StudentDto;
import me.zhengjie.modules.system.domain.Dept;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @website https://el-admin.vip
 * @author yangyao
 * @date 2020-09-20
 **/
public interface GzmuAnnUserContactsRepository extends JpaRepository<GzmuAnnUserContacts, Long>, JpaSpecificationExecutor<GzmuAnnUserContacts> {
    @Query(value = "select s.*,a.avatar_url from gzmu_ann_user_contacts u,gzmu_ann_student s,sys_user a where u.consent=1 and s.user_id=u.user_id  and s.user_id=a.user_id   and u.user_contacts_id= :myId and s.name like %:name% " +
            "union all select  s.*,a.avatar_url from gzmu_ann_user_contacts u,gzmu_ann_student s,sys_user a where u.consent=1 and s.user_id=u.user_contacts_id and s.user_id=a.user_id and u.user_id= :myId and s.name like %:name% ", nativeQuery = true)
    List<Map<String, Object>> findContactsByName(Long myId, String name, Pageable pageable);

    @Query(value = "select u.id,a.username,a.phone,a.user_id,a.avatar_url from gzmu_ann_user_contacts u,sys_user a where u.user_id=a.user_id and u.consent=0 and u.user_contacts_id=:myId", nativeQuery = true)
    List<Map<String, Object>> findContactsApply(Long myId,  Pageable pageable);

    @Transactional
    @Modifying
    @Query(value = "update gzmu_ann_user_contacts set consent = :consent where id = :contactId",nativeQuery = true)
    void alterByContactsId(Long contactId, Integer consent);

    @Query(value = "select * from gzmu_ann_user_contacts where (user_id= :myId and user_contacts_id=:userId) or (user_id=:userId and user_contacts_id=:myId) ", nativeQuery = true)
    GzmuAnnUserContacts findByUserId(Long myId, Long userId);
    /**
     * 根据userID 查询
     * @param myId 用户ID
     * @return
     */
    @Query(value = "select s.*,a.avatar_url from gzmu_ann_user_contacts u,gzmu_ann_student s,sys_user a where u.consent=1 and s.user_id=u.user_id and s.user_id=a.user_id and u.user_contacts_id= :myId " +
            "union all select  s.*,a.avatar_url from gzmu_ann_user_contacts u,gzmu_ann_student s,sys_user a where u.consent=1 and s.user_id=u.user_contacts_id and s.user_id=a.user_id and u.user_contacts_id= :myId", nativeQuery = true)
    List<Map<String,Object>> findByStuMyId(@Param("myId") Long myId, Pageable pageable);

    /**
     * 根据userID 查询
     * @param myId 用户ID
     * @return
     */
    @Query(value = "select t.*,a.avatar_url from gzmu_ann_user_contacts u,gzmu_ann_teacher t,sys_user a where u.consent=1 and t.user_id=u.user_id and t.user_id=a.user_id and u.user_contacts_id= :myId " +
            "union all select  t.*,a.avatar_url from gzmu_ann_user_contacts u,gzmu_ann_teacher t,sys_user a where u.consent=1 and t.user_id=u.user_contacts_id and t.user_id=a.user_id and u.user_contacts_id= :myId", nativeQuery = true)
    List<Map<String,Object>> findByTeaMyId(@Param("myId") Long myId, Pageable pageable);

    @Query(value = "select * from gzmu_ann_user_contacts where user_id= :myId and user_contacts_id=:userId", nativeQuery = true)
    GzmuAnnUserContacts findByMyId(Long myId, Long userId);

}
